package zy.dao.money.bank.impl;

import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.money.bank.BankRunDAO;
import zy.entity.money.bank.T_Money_BankRun;
import zy.util.StringUtil;

@Repository
public class BankRunDAOImpl extends BaseDaoImpl implements BankRunDAO{

	@Override
	public Integer count(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_money_bankrun t");
		sql.append(" JOIN t_money_bank ba ON ba_code = br_ba_code AND ba.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		if (StringUtil.isNotEmpty(params.get("begindate"))) {
			sql.append(" AND br_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(params.get("enddate"))) {
			sql.append(" AND br_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(params.get("ba_code"))) {
			sql.append(" AND t.br_ba_code=:ba_code");
		}
		if (StringUtil.isNotEmpty(params.get("br_number"))) {
			sql.append(" AND INSTR(br_number,:br_number) > 0");
		}
		sql.append(" AND ba_shop_code = :shop_code ");//上级店铺编号
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Money_BankRun> list(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT br_id,br_date,br_number,br_bt_code,br_manager,br_enter,br_out,br_balance,br_remark,br_sysdate,br_shop_code,br_ba_code,t.companyid,ba_name AS bank_name,");
		sql.append(" (SELECT dt_name FROM common_dict WHERE dt_code = br_bt_code AND dt_type = 'BANK_RUN' LIMIT 1) AS bt_name");
		sql.append(" FROM t_money_bankrun t");
		sql.append(" JOIN t_money_bank ba ON ba_code = br_ba_code AND ba.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		if (StringUtil.isNotEmpty(params.get("begindate"))) {
			sql.append(" AND br_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(params.get("enddate"))) {
			sql.append(" AND br_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(params.get("ba_code"))) {
			sql.append(" AND t.br_ba_code=:ba_code");
		}
		if (StringUtil.isNotEmpty(params.get("br_number"))) {
			sql.append(" AND INSTR(br_number,:br_number) > 0");
		}
		sql.append(" AND ba_shop_code = :shop_code ");//上级店铺编号
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY br_id DESC");
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Money_BankRun.class));
	}
	
	@Override
	public void save(T_Money_BankRun bankRun) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_money_bankrun");
		sql.append(" (br_date,br_number,br_bt_code,br_manager,br_ba_code,br_enter,br_out,br_balance,br_remark,br_sysdate,br_shop_code,companyid)");
		sql.append(" VALUES(:br_date,:br_number,:br_bt_code,:br_manager,:br_ba_code,:br_enter,:br_out,:br_balance,:br_remark,:br_sysdate,:br_shop_code,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(bankRun),holder);
		bankRun.setBr_id(holder.getKey().intValue());
	}

	@Override
	public void save(List<T_Money_BankRun> bankRuns) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_money_bankrun");
		sql.append(" (br_date,br_number,br_bt_code,br_manager,br_ba_code,br_enter,br_out,br_balance,br_remark,br_sysdate,br_shop_code,companyid)");
		sql.append(" VALUES(:br_date,:br_number,:br_bt_code,:br_manager,:br_ba_code,:br_enter,:br_out,:br_balance,:br_remark,:br_sysdate,:br_shop_code,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(bankRuns.toArray()));
	}

	@Override
	public List<T_Money_BankRun> report_sum(Map<String, Object> params){
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT br_ba_code,ba_name AS bank_name,SUM(br_enter) AS br_enter,SUM(br_out) AS br_out ");
		sql.append(" FROM t_money_bankrun t");
		sql.append(" JOIN t_money_bank ba ON ba_code = br_ba_code AND ba.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(params.get("begindate"))) {
			sql.append(" AND br_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(params.get("enddate"))) {
			sql.append(" AND br_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(params.get("ba_code"))) {
			sql.append(" AND t.br_ba_code=:ba_code");
		}
		if (StringUtil.isNotEmpty(params.get("br_manager"))) {
			sql.append(" AND br_manager=:br_manager");
		}
		if (StringUtil.isNotEmpty(params.get("type"))) {
			params.put("types", Arrays.asList(params.get("type").toString().split(",")));
			sql.append(" AND br_bt_code IN (:types) ");
		}
		sql.append(" AND ba.ba_shop_code = :shop_code");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY ba_code");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Money_BankRun.class));
	}
	
}
